﻿using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using MyFloridaHouse.DataAccess.Extensions;
using MyFloridaHouse.libSvDTO;
using MyFloridaHouse.LibDTO;
using System.Data.SqlClient;

namespace MyFloridaHouse.DataAccess
{
    /// <summary>
    /// Data access layer storage operation methods for regions data
    /// </summary>
    public partial class DAL_CensusData
    {

        /// <summary>
        /// Gets the region data.
        /// </summary>
        /// <param name="iLayerID">The ilayerID.</param>
        /// <param name="iRegionID">The iregionID.</param>
        /// <returns>List of <see cref="RegionDataNameAndValueDTO"/></returns>
        /// <remarks>SPROC: usp_moGetRegionData</remarks>
        public List<RegionDataNameAndValueDTO> GetRegionData(int iLayerID, int iRegionID)
        {
            List<RegionDataNameAndValueDTO> itemList = new List<RegionDataNameAndValueDTO>();

            //string storedProcedureName = "usp_moGetRegionData";
            string storedProcedureName = "usp_tl10GetRegionData10";

            using (SqlConnection db = Connect())
            {
                SqlCommand StoredProcedureCommand = new SqlCommand(storedProcedureName, db);
                StoredProcedureCommand.CommandType = CommandType.StoredProcedure;
                SqlParameter dataPartIDParm = StoredProcedureCommand.Parameters.AddWithValue("iLayerID", iLayerID);
                dataPartIDParm.Direction = ParameterDirection.Input;
                SqlParameter dataPartIDParm2 = StoredProcedureCommand.Parameters.AddWithValue("iRegionID", iRegionID);
                dataPartIDParm2.Direction = ParameterDirection.Input;


                db.Open();
                using (SqlDataReader rdr = StoredProcedureCommand.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        itemList.Add(RegionDataNameAndValueDTODataContract.DataContract(rdr));
                    }
                }
            }

            //Database db = Connect();
            ////DbCommand dbCommand = db.GetStoredProcCommand("usp_moGetRegionData");
            //DbCommand dbCommand = db.GetStoredProcCommand("usp_tl10GetRegionData10");
            //db.AddInParameter(dbCommand, "iLayerID", DbType.String, iLayerID);
            //db.AddInParameter(dbCommand, "iRegionID", DbType.String, iRegionID);
            //using (IDataReader rdr = db.ExecuteReader(dbCommand))
            //{
            //    while (rdr.Read())
            //    {
            //        itemList.Add(RegionDataNameAndValueDTODataContract.DataContract(rdr));

            //    }
            //}
            return itemList;
        }

        /// <summary>
        /// Gets the regions data in view.
        /// </summary>
        /// <param name="iTargetLayerID">The itargetlayerID.</param>
        /// <param name="iRegionIDList">The iregionID list.</param>
        /// <param name="iSourceLayerID">The isourcelayerID.</param>
        /// <param name="iDictionaryIDList">The idictionaryID list.</param>
        /// <returns>List of <see cref="RegionDataDTO"/></returns>
        /// <remarks>SPROC: usp_GetRegionsDataInView</remarks>
        public List<RegionDataDTO> getRegionsDataInView(int iTargetLayerID, string iRegionIDList, string iSourceLayerIDList, string iDictionaryIDList)
        {
            List<RegionDataDTO> itemList = new List<RegionDataDTO>();

            string storedProcedureName = "usp_tl10GetRegionsDataInView10";

            using (SqlConnection db = Connect())
            {
                SqlCommand StoredProcedureCommand = new SqlCommand(storedProcedureName, db);
                StoredProcedureCommand.CommandType = CommandType.StoredProcedure;
                SqlParameter dataPartIDParm = StoredProcedureCommand.Parameters.AddWithValue("iTargetLayerID", iTargetLayerID);
                dataPartIDParm.Direction = ParameterDirection.Input;
                SqlParameter dataPartIDParm2 = StoredProcedureCommand.Parameters.AddWithValue("iRegionIDList", iRegionIDList);
                dataPartIDParm2.Direction = ParameterDirection.Input;
                SqlParameter dataPartIDParm3 = StoredProcedureCommand.Parameters.AddWithValue("iSourceLayerIDList", iSourceLayerIDList);
                dataPartIDParm3.Direction = ParameterDirection.Input;
                SqlParameter dataPartIDParm4 = StoredProcedureCommand.Parameters.AddWithValue("iDictionaryIDList", iDictionaryIDList);
                dataPartIDParm4.Direction = ParameterDirection.Input;

                db.Open();
                using (SqlDataReader rdr = StoredProcedureCommand.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        itemList.Add(RegionDataDTODataContract.DataContract(rdr));
                    }
                }
            }

            //Database db = Connect();
            //DbCommand dbCommand = db.GetStoredProcCommand("usp_tl10GetRegionsDataInView10"); 
            //db.AddInParameter(dbCommand, "iTargetLayerID", DbType.Int32, iTargetLayerID);
            //db.AddInParameter(dbCommand, "iRegionIDList", DbType.String, iRegionIDList);
            //db.AddInParameter(dbCommand, "iSourceLayerIDList", DbType.String, iSourceLayerID);
            //db.AddInParameter(dbCommand, "iDictionaryIDList", DbType.String, iDictionaryIDList);
            //using (IDataReader rdr = db.ExecuteReader(dbCommand))
            //{
            //    while (rdr.Read())
            //    {
            //        itemList.Add(RegionDataDTODataContract.DataContract(rdr));
            //    }
            //}

            return itemList;
        }

        /// <summary>
        /// Gets the regions in view.
        /// </summary>
        /// <param name="iZoomLevel">The izoomlevel.</param>
        /// <param name="iLayerID">The ilayerID.</param>
        /// <param name="iNorth">The inorth.</param>
        /// <param name="iEast">The ieast.</param>
        /// <param name="iSouth">The isouth.</param>
        /// <param name="iWest">The iwest.</param>
        /// <returns>List of <see cref="RegionDTO"/></returns>
        /// <remarks>SPROC: usp_moGetRegionsInView</remarks>
        public List<RegionDTO> getRegionsInView(double iZoomlevel, int iLayerID, double iNorth, double iEast, double iSouth, double iWest)
        {
            List<RegionDTO> itemList = new List<RegionDTO>();
            string storedProcedureName = "usp_tl10GetRegionsDataInView10";

            using (SqlConnection db = Connect())
            {
                SqlCommand StoredProcedureCommand = new SqlCommand(storedProcedureName, db);
                StoredProcedureCommand.CommandType = CommandType.StoredProcedure;
                SqlParameter dataPartIDParm = StoredProcedureCommand.Parameters.AddWithValue("iZoomlevel", iZoomlevel);
                dataPartIDParm.Direction = ParameterDirection.Input;
                SqlParameter dataPartIDParm2 = StoredProcedureCommand.Parameters.AddWithValue("iLayerID", iLayerID);
                dataPartIDParm2.Direction = ParameterDirection.Input;
                SqlParameter dataPartIDParm3 = StoredProcedureCommand.Parameters.AddWithValue("iNorth", iNorth);
                dataPartIDParm3.Direction = ParameterDirection.Input;
                SqlParameter dataPartIDParm4 = StoredProcedureCommand.Parameters.AddWithValue("iEast", iEast);
                dataPartIDParm4.Direction = ParameterDirection.Input;
                SqlParameter dataPartIDParm5 = StoredProcedureCommand.Parameters.AddWithValue("iSouth", iSouth);
                dataPartIDParm5.Direction = ParameterDirection.Input;
                SqlParameter dataPartIDParm6 = StoredProcedureCommand.Parameters.AddWithValue("iWest", iWest);
                dataPartIDParm6.Direction = ParameterDirection.Input;

                db.Open();
                using (SqlDataReader rdr = StoredProcedureCommand.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        itemList.Add(RegionDTODataContract.DataContract(rdr));
                    }
                }
            }

            //Database db = Connect();

            //DbCommand dbCommand = db.GetStoredProcCommand("usp_moGetRegionsInView");
            //db.AddInParameter(dbCommand, "iZoomlevel", DbType.Double, iZoomLevel);
            //db.AddInParameter(dbCommand, "iLayerID", DbType.Int32, iLayerID);
            //db.AddInParameter(dbCommand, "iNorth", DbType.Double, iNorth);
            //db.AddInParameter(dbCommand, "iEast", DbType.Double, iEast);
            //db.AddInParameter(dbCommand, "iSouth", DbType.Double, iSouth);
            //db.AddInParameter(dbCommand, "iWest", DbType.Double, iWest);
            //using (IDataReader rdr = db.ExecuteReader(dbCommand))
            //{
            //    while (rdr.Read())
            //    {
            //        itemList.Add(RegionDTODataContract.DataContract(rdr));
            //    }
            //}

            return itemList;
        }

        /// <summary>
        /// Gets the regions shapes in view.
        /// </summary>
        /// <param name="iZoomLevel">The i zoom level.</param>
        /// <param name="iLayerID">The i layer ID.</param>
        /// <param name="iNorth">The i north.</param>
        /// <param name="iEast">The i east.</param>
        /// <param name="iSouth">The i south.</param>
        /// <param name="iWest">The i west.</param>
        /// <returns></returns>
        public List<RegionShapeDTO> GetRegionsShapesInView(double iZoomlevel, int iLayerID, double iNorth, double iEast, double iSouth, double iWest)
        {
            List<RegionShapeDTO> itemList = new List<RegionShapeDTO>();

            //string storedProcedureName = "usp_moGetRegionsShapesInView";
            string storedProcedureName = "usp_tl10GetRegionsShapesInView10";

            using (SqlConnection db = Connect())
            {
                SqlCommand StoredProcedureCommand = new SqlCommand(storedProcedureName, db);
                StoredProcedureCommand.CommandType = CommandType.StoredProcedure;
                SqlParameter dataPartIDParm = StoredProcedureCommand.Parameters.AddWithValue("iZoomlevel", iZoomlevel);
                dataPartIDParm.Direction = ParameterDirection.Input;
                SqlParameter dataPartIDParm2 = StoredProcedureCommand.Parameters.AddWithValue("iLayerID", iLayerID);
                dataPartIDParm2.Direction = ParameterDirection.Input;
                SqlParameter dataPartIDParm3 = StoredProcedureCommand.Parameters.AddWithValue("iNorth", iNorth);
                dataPartIDParm3.Direction = ParameterDirection.Input;
                SqlParameter dataPartIDParm4 = StoredProcedureCommand.Parameters.AddWithValue("iEast", iEast);
                dataPartIDParm4.Direction = ParameterDirection.Input;
                SqlParameter dataPartIDParm5 = StoredProcedureCommand.Parameters.AddWithValue("iSouth", iSouth);
                dataPartIDParm5.Direction = ParameterDirection.Input;
                SqlParameter dataPartIDParm6 = StoredProcedureCommand.Parameters.AddWithValue("iWest", iWest);
                dataPartIDParm6.Direction = ParameterDirection.Input;

                db.Open();
                using (SqlDataReader rdr = StoredProcedureCommand.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        itemList.Add(RegionShapeDTODataContract.DataContract(rdr));
                    }
                }
            }
            //Database db = Connect();

            ////DbCommand dbCommand = db.GetStoredProcCommand("usp_moGetRegionsShapesInView");
            //DbCommand dbCommand = db.GetStoredProcCommand("usp_tl10GetRegionsShapesInView10");
            //db.AddInParameter(dbCommand, "iZoomlevel", DbType.Double, iZoomLevel);
            //db.AddInParameter(dbCommand, "iLayerID", DbType.Int32, iLayerID);
            //db.AddInParameter(dbCommand, "iNorth", DbType.Double, iNorth);
            //db.AddInParameter(dbCommand, "iEast", DbType.Double, iEast);
            //db.AddInParameter(dbCommand, "iSouth", DbType.Double, iSouth);
            //db.AddInParameter(dbCommand, "iWest", DbType.Double, iWest);
            //using (IDataReader rdr = db.ExecuteReader(dbCommand))
            //{
            //    while (rdr.Read())
            //    {
            //        itemList.Add(RegionShapeDTODataContract.DataContract(rdr));
            //    }
            //}

            return itemList;
        }

        //Todo: remove unused code
        #region Unused code

        /// <summary>
        /// [Obsolete] Gets the layer data dictionary.
        /// </summary>
        /// <returns></returns>
        /// <remarks>[Obsolete] SPROC: usp_GetConfigLayerDataDictionary</remarks>
        public List<DataDictionaryDTO> GetLayerDataDictionary()
        {
            List<DataDictionaryDTO> itemList = new List<DataDictionaryDTO>();

            //string storedProcedureName = "usp_GetConfigLayerDataDictionary";
            string storedProcedureName = "usp_tl10GetConfigLayerDataDictionary10";

            SqlConnection db = Connect();
            SqlCommand StoredProcedureCommand = new SqlCommand(storedProcedureName, db);
            StoredProcedureCommand.CommandType = CommandType.StoredProcedure;
            db.Open();
            using (SqlDataReader rdr = StoredProcedureCommand.ExecuteReader())
            {
                while (rdr.Read())
                {
                    itemList.Add(DataDictionaryDTODataContract.DataContract(rdr));
                }
            }

            //Database db = Connect();
            ////DbCommand dbCommand = db.GetStoredProcCommand("usp_GetConfigLayerDataDictionary");
            //DbCommand dbCommand = db.GetStoredProcCommand("usp_tl10GetConfigLayerDataDictionary10");
            //dbCommand.CommandTimeout = 60000;
            //using (IDataReader rdr = db.ExecuteReader(dbCommand))
            //{
            //    while (rdr.Read())
            //    {
            //        itemList.Add(DataDictionaryDTODataContract.DataContract(rdr));
            //    }
            //}
            return itemList;
        }

        /// <summary>
        /// [Obsolete] Gets the regions.
        /// </summary>
        /// <param name="iLayer">The i layer.</param>
        /// <returns></returns>
        /// <remarks>[Obsolete] SPROC: usp_moGetRegions</remarks>
        public List<RegionDTO> GetRegions(LayerDTO iLayer)
        {
            List<RegionDTO> itemList = new List<RegionDTO>();

            string storedProcedureName = "usp_moGetRegions";

            using (SqlConnection db = Connect())
            {
                SqlCommand StoredProcedureCommand = new SqlCommand(storedProcedureName, db);
                StoredProcedureCommand.CommandType = CommandType.StoredProcedure;
                SqlParameter dataPartIDParm = StoredProcedureCommand.Parameters.AddWithValue("iLayerID", iLayer.layerid);
                dataPartIDParm.Direction = ParameterDirection.Input;


                db.Open();
                using (SqlDataReader rdr = StoredProcedureCommand.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        itemList.Add(RegionDTODataContract.DataContract(rdr));
                    }
                }
            }

            //Database db = Connect();
            //DbCommand dbCommand = db.GetStoredProcCommand("usp_moGetRegions");
            //db.AddInParameter(dbCommand, "iLayerID", DbType.Int32, iLayer.layerid);
            //using (IDataReader rdr = db.ExecuteReader(dbCommand))
            //{
            //    while (rdr.Read())
            //    {
            //        itemList.Add(RegionDTODataContract.DataContract(rdr));


            //    }
            //}

            return itemList;
        }

        /// <summary>
        /// [Obsolete] Gets the region I ds in view.
        /// </summary>
        /// <param name="iZoomLevel">The i zoom level.</param>
        /// <param name="iLayerID">The i layer ID.</param>
        /// <param name="iNorth">The i north.</param>
        /// <param name="iEast">The i east.</param>
        /// <param name="iSouth">The i south.</param>
        /// <param name="iWest">The i west.</param>
        /// <returns></returns>
        /// <remarks>[Obsolete] SPROC: usp_moGetregionIDsInView</remarks>
        public List<RegionDTO> GetRegionIDsInView(double iZoomlevel, int iLayerID, double iNorth, double iEast, double iSouth, double iWest)
        {
            //List<int> itemList = new List<int>();
            List<RegionDTO> itemList = new List<RegionDTO>();

            string storedProcedureName = "usp_moGetregionIDsInView";

            using (SqlConnection db = Connect())
            {
                SqlCommand StoredProcedureCommand = new SqlCommand(storedProcedureName, db);
                StoredProcedureCommand.CommandType = CommandType.StoredProcedure;
                SqlParameter dataPartIDParm = StoredProcedureCommand.Parameters.AddWithValue("iZoomlevel", iZoomlevel);
                dataPartIDParm.Direction = ParameterDirection.Input;
                SqlParameter dataPartIDParm2 = StoredProcedureCommand.Parameters.AddWithValue("iLayerID", iLayerID);
                dataPartIDParm2.Direction = ParameterDirection.Input;
                SqlParameter dataPartIDParm3 = StoredProcedureCommand.Parameters.AddWithValue("iNorth", iNorth);
                dataPartIDParm3.Direction = ParameterDirection.Input;
                SqlParameter dataPartIDParm4 = StoredProcedureCommand.Parameters.AddWithValue("iEast", iEast);
                dataPartIDParm4.Direction = ParameterDirection.Input;
                SqlParameter dataPartIDParm5 = StoredProcedureCommand.Parameters.AddWithValue("iSouth", iSouth);
                dataPartIDParm5.Direction = ParameterDirection.Input;
                SqlParameter dataPartIDParm6 = StoredProcedureCommand.Parameters.AddWithValue("iWest", iWest);
                dataPartIDParm6.Direction = ParameterDirection.Input;

                db.Open();
                using (SqlDataReader rdr = StoredProcedureCommand.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        itemList.Add(RegionDTODataContract.DataContract(rdr));
                    }
                }
            }

            //Database db = Connect();
            //DbCommand dbCommand = db.GetStoredProcCommand("usp_moGetregionIDsInView");
            //db.AddInParameter(dbCommand, "iZoomlevel", DbType.Double, iZoomLevel);
            //db.AddInParameter(dbCommand, "iLayerID", DbType.Int32, iLayerID);
            //db.AddInParameter(dbCommand, "iNorth", DbType.Double, iNorth);
            //db.AddInParameter(dbCommand, "iEast", DbType.Double, iEast);
            //db.AddInParameter(dbCommand, "iSouth", DbType.Double, iSouth);
            //db.AddInParameter(dbCommand, "iWest", DbType.Double, iWest);
            //using (IDataReader rdr = db.ExecuteReader(dbCommand))
            //{
            //    while (rdr.Read())
            //    {
            //        //regionList.Add(rdr["regionID"] is DBNull ? 0 : Convert.ToInt32(rdr["regionID"]));
            //        itemList.Add(RegionDTODataContract.DataContract(rdr));
            //    }
            //}

            return itemList;
        }

        /// <summary>
        /// [Obsolete] Updates the layer region.
        /// </summary>
        /// <param name="iLayerID">The i layer ID.</param>
        /// <param name="iRegionID">The i region ID.</param>
        /// <param name="iCenterLngX">The i center LNG X.</param>
        /// <param name="iCenterLatY">The i center lat Y.</param>
        /// <param name="iMapObject">The i map object.</param>
        /// <param name="iNorth">The i north.</param>
        /// <param name="iEast">The i east.</param>
        /// <param name="iSouth">The i south.</param>
        /// <param name="iWest">The i west.</param>
        /// <returns></returns>
        /// <remarks>[Obsolete] SPROC: usp_UpdateRegion</remarks>
        public List<RegionDTO> UpdateLayerRegion(int iLayerID, int iRegionID, double iCenterLngX, double iCenterLatY, string iMapObject
                                                        , double iNorth, double iEast, double iSouth, double iWest)
        {
            List<RegionDTO> itemList = new List<RegionDTO>();

            string storedProcedureName = "usp_UpdateRegion";

            using (SqlConnection db = Connect())
            {
                SqlCommand StoredProcedureCommand = new SqlCommand(storedProcedureName, db);
                StoredProcedureCommand.CommandType = CommandType.StoredProcedure;
                SqlParameter dataPartIDParm = StoredProcedureCommand.Parameters.AddWithValue("iLayerID", iLayerID);
                dataPartIDParm.Direction = ParameterDirection.Input;
                SqlParameter dataPartIDParm2 = StoredProcedureCommand.Parameters.AddWithValue("iRegionID", iRegionID);
                dataPartIDParm2.Direction = ParameterDirection.Input;
                SqlParameter dataPartIDParm3 = StoredProcedureCommand.Parameters.AddWithValue("iCenterLngX", iCenterLngX);
                dataPartIDParm3.Direction = ParameterDirection.Input;
                SqlParameter dataPartIDParm4 = StoredProcedureCommand.Parameters.AddWithValue("iCenterLatY", iCenterLatY);
                dataPartIDParm4.Direction = ParameterDirection.Input;
                SqlParameter dataPartIDParm5 = StoredProcedureCommand.Parameters.AddWithValue("iMapObject", iMapObject);
                dataPartIDParm5.Direction = ParameterDirection.Input;
                SqlParameter dataPartIDParm6 = StoredProcedureCommand.Parameters.AddWithValue("iLatYmax", iNorth);
                dataPartIDParm6.Direction = ParameterDirection.Input;
                SqlParameter dataPartIDParm7 = StoredProcedureCommand.Parameters.AddWithValue("iLngXmax", iEast);
                dataPartIDParm7.Direction = ParameterDirection.Input;
                SqlParameter dataPartIDParm8 = StoredProcedureCommand.Parameters.AddWithValue("iLatYmin", iSouth);
                dataPartIDParm8.Direction = ParameterDirection.Input;
                SqlParameter dataPartIDParm9 = StoredProcedureCommand.Parameters.AddWithValue("iLngXmin", iWest);
                dataPartIDParm9.Direction = ParameterDirection.Input;

                db.Open();
                using (SqlDataReader rdr = StoredProcedureCommand.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        itemList.Add(RegionDTODataContract.DataContract(rdr));
                    }
                }
            }
            //Database db = Connect();
            //DbCommand dbCommand = db.GetStoredProcCommand("usp_UpdateRegion");
            //db.AddInParameter(dbCommand, "iLayerID", DbType.Int32, iLayerID);
            //db.AddInParameter(dbCommand, "iRegionID", DbType.Int32, iRegionID);
            //db.AddInParameter(dbCommand, "iCenterLngX", DbType.Double, iCenterLngX);
            //db.AddInParameter(dbCommand, "iCenterLatY", DbType.Double, iCenterLatY);
            //db.AddInParameter(dbCommand, "iMapObject", DbType.String, iMapObject);
            //db.AddInParameter(dbCommand, "iLatYmax", DbType.String, iNorth);
            //db.AddInParameter(dbCommand, "iLngXmax", DbType.String, iEast);
            //db.AddInParameter(dbCommand, "iLatYmin", DbType.String, iSouth);
            //db.AddInParameter(dbCommand, "iLngXmin", DbType.String, iWest);
            //using (IDataReader rdr = db.ExecuteReader(dbCommand))
            //{
            //    while (rdr.Read())
            //    {
            //        itemList.Add(RegionDTODataContract.DataContract(rdr));
            //    }
            //}

            return itemList;
        }

        #endregion
    }
}
